IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pSearchBill'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pSearchBill
GO

CREATE PROC dbo.pSearchBill
	@vdatFromDate			DATETIME = NULL,
	@vdatToDate				DATETIME = NULL,
	@vintBillId				INT = NULL,
	@vintBillIndex			INT = NULL,
	@vbitIsPrinted			BIT = NULL,
	@vstrCustomerName		NVARCHAR(255) = NULL,
	@vintCustomerPhone		INT = NULL,
	@vstrProductName		NVARCHAR(255) = NULL
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/

  DECLARE
    @cstrThisProc              NVARCHAR(128),
    @cintErrGeneral            INT

  SET @cstrThisProc            = N'pSearchBill'
  SET @cintErrGeneral          = 50000  -- General error. Used when an
                                        -- error has occurred and no other
                                        -- specific error condition is
                                        -- assigned.

/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @intReturn      INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/
	DECLARE @vstrCustomerPhone AS CHAR(20)

/*_______________________________ MAIN _________________________________*/
	SET @vstrCustomerPhone = CAST(@vintCustomerPhone AS CHAR(20))
	
	SELECT DISTINCT		
		Bill.Id, 
		Bill.DayIndex, 
		Bill.SaleTime, 
		Bill.CustomerName, 
		Bill.CustomerPhone,
		Bill.CustomerAddress, 
		Bill.IsPrinted,
		TotalPrice = SUM(BillDetail.Amount * BillDetail.SalePrice),
		RowNumber = ROW_NUMBER() OVER(ORDER BY Bill.Id DESC)
	FROM Bill
	
	INNER JOIN BillDetail
		ON Bill.Id = BillDetail.BillId
	
	INNER JOIN Product
		ON BillDetail.ProductId = Product.Id
	
	WHERE
		((@vdatFromDate IS NULL) OR (DATEDIFF(DAY, @vdatFromDate, Bill.SaleTime) >= 0))
		AND ((@vdatToDate IS NULL) OR (DATEDIFF(DAY, Bill.SaleTime, @vdatToDate) >= 0))	
		AND ((@vintBillId IS NULL) OR (Bill.Id = @vintBillId))
		AND ((@vintBillIndex IS NULL) OR (Bill.DayIndex = @vintBillIndex))		
		AND ((@vbitIsPrinted IS NULL) OR (Bill.IsPrinted = @vbitIsPrinted))	
		AND ((@vstrCustomerName IS NULL) OR (Bill.CustomerName LIKE '%' + @vstrCustomerName + '%'))
		AND ((@vintCustomerPhone IS NULL) OR (CAST(Bill.CustomerPhone AS CHAR(20)) LIKE '%' + @vstrCustomerPhone + '%'))
		AND ((@vstrProductName IS NULL) OR (Product.[Name] LIKE '%' + @vstrProductName + '%'))	
	
	GROUP BY Bill.Id, Bill.DayIndex, Bill.SaleTime, Bill.CustomerName, Bill.CustomerPhone,
		Bill.CustomerAddress, Bill.IsPrinted		

	ORDER BY Bill.Id DESC
  -- Return value 0 indicates success
  SET @intReturn = 0

lblExit:

  RETURN @intReturn

/*___________________________ ERROR HANDLER ____________________________*/
lblError:
  -- An error has occurred.  On entering this section @intErrNo should
  -- already hold the error number

  -- By default, if there are any open transactions then roll them back
  IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN

  END

  -- If return value has not been explicitly set, then use the error
  -- number as return value to signify failure
  IF ((@intReturn IS NULL) OR (@intReturn = 0)) BEGIN
    SET @intReturn = @intErrNo

  END

 GOTO lblExit


